package cn.hxzy.util;

import java.io.Serializable;
import java.lang.reflect.AccessibleObject;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class DBUtil {
    /**
     * 查询数量
     *
     * @param connection 数据库连接
     * @param whereSql   sql 的 where 条件 如：where pid =？
     * @param values     sql中？ 填充的值
     * @param c          针对的实体类型
     * @param <T>
     * @return 数据库的数量
     * @throws SQLException
     */
    public static <T> long count(Connection connection, String whereSql, Object[] values, Class<T> c) throws SQLException {
        //count(1、启动数据库连接方式；2、给出查询要求（eg：id=?);3、new object[]{}（应该是给对应的参数）;4、类名.class)
        /*SQL拼写*/
        StringBuffer stringBuffer = new StringBuffer("select count(0) from " + StringUtil.underscoreName(c.getSimpleName()) + " " + whereSql);
        PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());//SQL拼接
        System.out.println(stringBuffer);
        if (values != null) for (int i = 0; i < values.length; i++) {//添加问号
            System.out.print(i + ":" + values[i] + "; ");
            statement.setObject(i + 1, values[i]);
        }
        ResultSet resultSet = statement.executeQuery();
        /*输出结果集*/
        if (resultSet.next()) {
            long count = resultSet.getLong(1);
            resultSet.close();
            statement.close();
            return count;
        }
        return 0;
    }

    /**
     * 查询集合
     *
     * @param <T>
     * @param connection 数据库连接
     * @param whereSql   sql 的 where 条件 如：where pid =？
     * @param values     sql中？ 填充的值
     * @param c          针对的实体类型
     * @return 对应的对象集合
     * @throws Exception
     */
    public static <T> List<T> select(Connection connection, String whereSql, Object[] values, Class<T> c) throws Exception {//查询所有员工
        /*SQL拼写*/
        StringBuffer stringBuffer = new StringBuffer("select ");
        Field[] fields = c.getDeclaredFields();//获取类的声明属性
        for (Field field : fields) {
            stringBuffer.append(StringUtil.underscoreName(field.getName()) + ",");//属性拼接，加上“,”
        }
        stringBuffer.deleteCharAt(stringBuffer.length() - 1);//删除最后一个字符，原因是防止最后一个“，”拼接
        stringBuffer.append(" from " + StringUtil.underscoreName(c.getSimpleName()) + " " + whereSql);
        PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());//
        System.out.println(stringBuffer);
        if (values != null) for (int i = 0; i < values.length; i++) {
            System.out.print(i + ":" + values[i] + "; ");
            statement.setObject(i + 1, values[i]);
        }
        ResultSet resultSet = statement.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();//ResultSetMetaData 结果集元数据（获取结果集里的详细信息）
        /*排列出数据列*/
        List<T> list = new ArrayList();
        while (resultSet.next()) {
            T obj = c.newInstance();//新建一个对应的实例，注意：会调用无参构造方法
            int colCount = metaData.getColumnCount();//获取到结果集的列数量

            for (int i = 0; i < colCount; i++) {
                String columnName = metaData.getColumnName(i + 1);//得到数据库的列名
                Field[] fs = c.getDeclaredFields();//获取类声明的属性
                AccessibleObject.setAccessible(fs, true);//得到最高权限方便修改
                for (Field f : fs) {
                    if (StringUtil.underscoreName(f.getName()).equals(columnName.toLowerCase())) {//判断输入的名字与对象里的是否对应
                        f.set(obj, resultSet.getObject(columnName));//将对象 obj 的属性 f.getName 改为 resultSet.getObject(columnName
                    }
                }
            }
            list.add(obj);
        }
        resultSet.close();
        statement.close();
        return list;
    }

    /**
     * 根据id查询对象
     *
     * @param connection 数据库连接
     * @param id         id的值
     * @param c          类型
     * @param <T>
     * @return 查询到的结果
     * @throws Exception
     */
    public static <T> T selectOne(Connection connection, Serializable id, Class<T> c) throws Exception {//Serializable 序列化    查询某个用户（通过名字，id等）
        /*SQL拼写*/
        StringBuffer stringBuffer = new StringBuffer("select ");
        Field[] fields = c.getDeclaredFields();//提取对象或类的属性 field=字段 ；declared=公告的
        for (Field field : fields) {
            stringBuffer.append(StringUtil.underscoreName(field.getName()) + ",");//underscore=强调
        }
        stringBuffer.deleteCharAt(stringBuffer.length() - 1);//删除字段的最后一个逗号
        stringBuffer.append(" from " + StringUtil.underscoreName(c.getSimpleName()) + " where id =" + id);//追加一个对象（？）
        PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());//prepareStatement=声明
        System.out.println(stringBuffer);
        ResultSet resultSet = statement.executeQuery();
        /*查找对应数据判断是否存在*/
        ResultSetMetaData metaData = resultSet.getMetaData();
        T obj = null;
        if (resultSet.next()) {
            obj = c.newInstance();//newInstance=发射
            int colCount = metaData.getColumnCount();//column count//行列的数量
            for (int i = 0; i < colCount; i++) {
                String columnName = metaData.getColumnName(i + 1);//column=栏
                Field[] fs = c.getDeclaredFields();
                AccessibleObject.setAccessible(fs, true);
                for (Field f : fs) {
                    if (StringUtil.underscoreName(f.getName()).equals(columnName.toLowerCase())) {
                        f.set(obj, resultSet.getObject(columnName));
                    }
                }
            }

        }
        resultSet.close();
        statement.close();
        return obj;
    }


    /**
     * 插入数据库一条数据
     *
     * @param connection 连接
     * @param obj        插入的对象
     * @return 数据库影响行数
     * @throws Exception
     */
    public static int insert(Connection connection, Object obj) throws Exception {//插入用户等
        //eg:"insert into user (name,login_name,login_password,role_id,creat_time) value (?,?,?,?,now())");
        /*拼接SQL语句*/
        StringBuffer stringBuffer = new StringBuffer("insert into ");
        Class clazz = obj.getClass();
        stringBuffer.append(StringUtil.underscoreName(clazz.getSimpleName()) + " (");//这里是提取出类的名字user（）下划线命名
//将user类需要的对象进行提取
        Field[] fields = clazz.getDeclaredFields();
        AccessibleObject.setAccessible(fields, true);//权限获取
        for (int i = 0; i < fields.length; i++) {
            if (fields[i].get(obj) == null)//判断属性为空就不插入数据库
                continue;//继续
            String fieldName = fields[i].getName();
            stringBuffer.append(StringUtil.underscoreName(fieldName) + ",");//underscoreName 下划线命名
        }
        stringBuffer.deleteCharAt(stringBuffer.length() - 1);
        //向控制台输出拼接的“）values（”
        stringBuffer.append(") values(");
        //添加之前 insert into里对象个数的’？‘
        List list = new ArrayList();
        for (int i = 0; i < fields.length; i++) {
            if (fields[i].get(obj) == null) {
                continue;
            }
            list.add(fields[i].get(obj));//获取obj的值
            stringBuffer.append(" ?,");
        }
        stringBuffer.deleteCharAt(stringBuffer.length() - 1);
        stringBuffer.append(")");
        System.out.println(stringBuffer);//输出SQL语句
        PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());//把上面数据转化成表数据（？）
        /*设置 statement 向数据库发送信息*/
        for (int i = 0; i < list.size(); i++) {
            System.out.print(i + ":" + list.get(i) + "; ");
            statement.setObject(i + 1, list.get(i));
        }
        int count = statement.executeUpdate();
        statement.close();
        return count;
    }

    /**
     * 根据 id 删除对象
     *
     * @param connection 连接
     * @param id         id
     * @param c          类型
     * @param <T>
     * @return 数据库影响行数
     * @throws Exception
     */
    public static <T> int delete(Connection connection, Serializable id, Class<T> c) throws Exception {
        String name = c.getSimpleName();
        /*把使用的SQL分开，使用对应情况直接带进去*/
        StringBuffer stringBuffer = new StringBuffer("delete from ");
        stringBuffer.append(StringUtil.underscoreName(name));
        stringBuffer.append(" where id = ?");
        System.out.println(stringBuffer);
        /*同理选择哪个id带进去删除，返回*/
        PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
        statement.setObject(1, id);
        int count = statement.executeUpdate();
        statement.close();
        return count;
    }

    /**
     * 根据id 修改对象的属性
     *
     * @param connection 连接
     * @param obj        对象
     * @return 数据库影响行数
     * @throws Exception
     */
    public static int update(Connection connection, Object obj) throws Exception {
        /*写sql*/
        /*"update   user    set    `name` = ?,role_id=?,login_password=?,update_time = now()   where id   =? ");*/
        StringBuffer stringBuffer = new StringBuffer("update ");
        Class clazz = obj.getClass();
        stringBuffer.append(StringUtil.underscoreName(clazz.getSimpleName()) + " set ");//写下表名
        Field[] fields = clazz.getDeclaredFields();
        List list = new ArrayList();//把属性包装为一个集合
        AccessibleObject.setAccessible(fields, true);
        Object id = 0;
        for (int i = 0; i < fields.length; i++) {//属性个数
            String fieldName = fields[i].getName();
            if (fieldName.equals("id")) {//输入id，查找对应对象
                id = fields[i].get(obj);
                continue;
            }
            if (fields[i].get(obj) == null) {
                continue;
            }
            list.add(fields[i].get(obj));
            stringBuffer.append(StringUtil.underscoreName(fieldName) + " = ?,");//`name` = ?,role_id=?,login_password=?,update_time = now()
        }
        stringBuffer.deleteCharAt(stringBuffer.length() - 1);
        stringBuffer.append(" where id = " + id);

        System.out.println(stringBuffer);
        PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
        /* 向数据库填充数据*/
        for (int i = 0; i < list.size(); i++) {
            System.out.print(i + ":" + list.get(i) + "; ");
            statement.setObject(i + 1, list.get(i));
        }
        int count = statement.executeUpdate();
        statement.close();
        return count;
    }
}
